<?php

namespace app\admin\controller;

use think\Db;
use think\Request;

class Product extends Base
{
    /**
     * 产品包展示
     * @return mixed|\think\response\Json
     */
    public function index(){
        if(request()->isAjax ()){
            $params = input('param.');
            $where = [
                'sg_id' => ['>',0]
            ];
            
            // 活动名称
            if(isset($params['school']) && $params['school'] != ''){
                $where['school_name|sg_school_erp'] = ['like', '%' . $params['school'] . '%'];
            }
            if(isset($params['school2']) && $params['school2'] != ''){
                $where['sg_good_coding'] = ['like', '%' . $params['school2'] . '%'];
            }
            if(isset($params['typename']) && $params['typename'] != ''){
                $where['good_type'] = ['like', '%' . $params['typename'] . '%'];
            }
            if(isset($params['year']) && $params['year'] != ''){
                $where['sg_year'] = $params['year'];
            }
            if(isset($params['status']) && $params['status'] != ''){
                $where['sg_status'] = $params['status'];
            }
            if(isset($params['start'])&&$params['start']!=""&&isset($params['end'])&&$params['end']=="")
            {
                $where['sg_updatetime'] = ['>= time',$params['start']];
            }
            if(isset($params['end'])&&$params['end']!=""&&isset($params['start'])&&$params['start']=="")
            {
                $where['sg_updatetime'] = ['<= time',$params['end']];
            }
            if(isset($params['start'])&&$params['start']!=""&&isset($params['end'])&&$params['end']!="")
            {
                $where['sg_updatetime'] = ['between time',[$params['start'],$params['end']]];
            }
            $Nowpage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $field = 'sg.*,school_name,good_name,good_thumb,good_type';
            $count = Db::table('up_school_good')->alias('sg')
                ->join('up_school s','sg.sg_school_erp=s.school_erp','LEFT')
                ->join('up_goods g','sg.sg_good_coding=g.good_coding','LEFT')
                ->where($where)
                ->count();// 获取总条数
            $lists = Db::table('up_school_good')->alias('sg')
                ->join('up_school s','sg.sg_school_erp=s.school_erp','LEFT')
                ->join('up_goods g','sg.sg_good_coding=g.good_coding','LEFT')
                ->where($where)
                ->page($Nowpage,$limits)
                ->order('sg_updatetime desc')
                ->field($field)
                ->select();

            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        return $this->fetch();
    }
    /**
     * 改状态（是否开启）
     * @author
     */
    public function changeSgStatus()
    {
        extract(input());
        $res = Db::table('up_school_good')
            ->where('sg_id',$id)
            ->update([
                'sg_updatetime' => date('Y-m-d H:i:s'),
                'sg_status' => $num
            ]);
        if($res){
            return json(['code' => 200, 'msg' => '成功']);
        }else{
            return json(['code' => 100, 'msg' => '失败']);
        }
    }
    /**
     * 删除
     * @author
     */
    public function sgDel()
    {
        extract(input());
        $res = Db::table('up_school_good')
            ->where('sg_id',$id)
            ->delete();
        if($res){
            return json(['code' => 200, 'msg' => '成功']);
        }else{
            return json(['code' => 100, 'msg' => '失败']);
        }
    }
    /**
     * 改校标
     * @return mixed|\think\response\Json
     */
    public function updateSign(){
        $id = input('id');
        $param = [
            'sg_sign'=>input('value'),
        ];
        $res = Db::name('school_good')->where('sg_id',$id)->update($param);
        if($res){
            return json(['code' => 200, 'msg' => '修改成功']);
        }else{
            return json(['code' => 100, 'msg' =>' 修改失败']);
        }
    }
    /**
     * 导入Excel
     * @return mixed
     */
    public function excelIn2(){
        set_time_limit(0);
        if(request()->file('file')){
            $file = request()->file('file');
            $info  = $file->getInfo();
            $file_path = $info['tmp_name'];
            //取出文件后缀
            $file_type = explode(".",$info['name']);
            $file_type = end($file_type);
            vendor("PHPExcel.PHPExcel.IOFactory");
            if( $file_type == 'xls' ){
                $inputFileType = 'Excel5';    //这个是读 xls的
            }else{
                $inputFileType = 'Excel2007';//这个是计xlsx的
            }
            $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($file_path);
            $objWorksheet = $objPHPExcel->getActiveSheet();
            $highestRow = $objWorksheet->getHighestRow();
//            print_r($highestRow);die;
            $time = date('Y-m-d H:i:s');
            $data = [];
            for ($i=2;$i<=$highestRow;$i++) {
                $a = $objPHPExcel->getActiveSheet ()->getCell ("E" . $i)->getValue ();//款号
                $b = $objPHPExcel->getActiveSheet ()->getCell ("A" . $i)->getValue ();//学校
                $c = $objPHPExcel->getActiveSheet ()->getCell ("I" . $i)->getValue ();//年份
                $d = $objPHPExcel->getActiveSheet ()->getCell ("J" . $i)->getValue ();//性别
                $e = $objPHPExcel->getActiveSheet ()->getCell ("O" . $i)->getValue ();//校标
                $f = $objPHPExcel->getActiveSheet ()->getCell ("M" . $i)->getValue ();//增不加
                if($a != '' && $b != '' && $c != '' && $d != '' && $f != ''){
                    $sex = 2;
                    if($d == '男'){
                        $sex = 1;
                    }
                    $inser = [
                        'sg_school_erp' => $b,
                        'sg_good_coding' => $a,
                        'sg_good_coding_less' => substr($a,1,12),
                        'sg_good_q' => 1,
                        'sg_sign' => $e,
                        'sg_year' => $c,
                        'sg_sex' => $sex,
                        'good_price' => $f,
                        'sg_time' => $time,
                        'sg_updatetime' => $time
                    ];
                    $inser2 = [
                        'sg_school_erp' => $b,
                        'sg_good_coding' => $a,
                        'sg_good_q' => 1,
                        'sg_sign' => $e,
                        'sg_year' => $c,
                        'sg_sex' => ['in',"$sex,3"]
                    ];
                    $have = Db::table('up_school_good')->where($inser2)->find();
                    if(empty($have)){
                        $data[] = $inser;
                    }else{
                        if($have['good_price'] != $f){
                            Db::table('up_school_good')->where($inser2)->setField('good_price',$f);
                        }
                    }
                }
            }
            if($data){
                $res = Db::table('up_school_good')->insertAll($data);
                if($res){
                    echo '/admin/Product/index';
                }else{
                    echo '/admin/Product/excelin2';
                }
            }else{
                echo '/admin/Product/index';
            }
        }
        return $this->fetch('product/excelin2');
    }
    /**
     * 去冻结
     * @return mixed
     */
    public function goDong(){
        if(Request::instance()->isPost()){
            $schools = input('schools');
            $years = input('years');
            $schools = trim($schools);
            $schools = trim($schools,',');
            $years = trim($years);
            $years = trim($years,',');
            $where = [
                'sg_school_erp' => ['in',$schools],
                'sg_status' => 1
            ];
            if($years) $where['sg_year'] = ['in',$years];
            Db::name('school_good')
                ->where($where)
                ->setField('sg_status',2);
            return json(['code' => 200, 'msg' => '操作成功']);
        }
        return $this->fetch('product/dong');
    }
    /**
     * q款和非q款
     * @return mixed|\think\response\Json
     */
    public function qq(){
        if(request()->isAjax ()){
            $params = input('param.');
            $where = [];

            // 活动名称
            if(isset($params['school']) && $params['school'] != ''){
                $where['school_name|school'] = ['like', '%' . $params['school'] . '%'];
            }
            if(isset($params['year']) && $params['year'] != ''){
                $where['year'] = $params['year'];
            }
            if(isset($params['code']) && $params['code'] != ''){
                $where['code|codeq'] = ['like', '%' . $params['code'] . '%'];
            }
            $Nowpage = input('page') ? input('page') : 1;
            $limits = input("limit") ? input("limit") : 10;
            $field = 'sg.*,school_name';
            $count = Db::name('school_year_code')->alias('sg')
                ->join('up_school s','sg.school=s.school_erp','LEFT')
                ->where($where)
                ->count();// 获取总条数
            $lists = Db::name('school_year_code')->alias('sg')
                ->join('up_school s','sg.school=s.school_erp','LEFT')
                ->where($where)
                ->page($Nowpage,$limits)
                ->order('time desc')
                ->field($field)
                ->select();

            return json(['code' => 220, 'msg' => '', 'count' => $count, 'data' => $lists]);
        }
        return $this->fetch();
    }
    /**
     * 导入Excel
     * @return mixed
     */
    public function excelIn(){
        set_time_limit(0);
        if(request()->file('file')){
            $file = request()->file('file');
            $info  = $file->getInfo();
            $file_path = $info['tmp_name'];
            //取出文件后缀
            $file_type = explode(".",$info['name']);
            $file_type = end($file_type);
            vendor("PHPExcel.PHPExcel.IOFactory");
            if( $file_type == 'xls' ){
                $inputFileType = 'Excel5';    //这个是读 xls的
            }else{
                $inputFileType = 'Excel2007';//这个是计xlsx的
            }
            $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($file_path);
            $objWorksheet = $objPHPExcel->getActiveSheet();
            $highestRow = $objWorksheet->getHighestRow();
//            print_r($highestRow);die;
            $time = date('Y-m-d H:i:s');
            $data = [];
            for ($i=2;$i<=$highestRow;$i++) {
                $school = $objPHPExcel->getActiveSheet ()->getCell ("A" . $i)->getValue ();//学校
                $year = $objPHPExcel->getActiveSheet ()->getCell ("C" . $i)->getValue ();//年份
                $codeq = $objPHPExcel->getActiveSheet ()->getCell ("D" . $i)->getValue ();//q款（表格里其实是非q款）
                $code = $objPHPExcel->getActiveSheet ()->getCell ("E" . $i)->getValue ();//非q款
                if($school != '' && $year != '' && $codeq != '' && $code != ''){
                    $inser = [
                        'school' => $school,
                        'year' => $year,
                        'codeq' => $codeq,
                        'code' => $code,
                        'time' => $time
                    ];
                    $inser2 = [
                        'school' => $school,
                        'year' => $year,
                        'codeq|code' => ['in',"$codeq,$code"]
                    ];
                    $have = Db::table('up_school_year_code')->where($inser2)->find();
                    if(empty($have)){
                        $data[] = $inser;
                    }
                }
            }
            if($data){
                $res = Db::table('up_school_year_code')->insertAll($data);
                if($res){
                    echo '/admin/Product/qq';
                }else{
                    echo '/admin/Product/excelin';
                }
            }else{
                echo '/admin/Product/qq';
            }
        }
        return $this->fetch('product/excelin');
    }
    //导出
    public function excelOutPro()
    {
        set_time_limit(0);//防止超时
        $params = input('param.');
        $where = [
            'sg_id' => ['>',0]
        ];
        // 活动名称
        if(isset($params['school']) && $params['school'] != ''){
            $where['school_name|sg_school_erp'] = ['like', '%' . $params['school'] . '%'];
        }
        if(isset($params['school2']) && $params['school2'] != ''){
            $where['sg_good_coding'] = ['like', '%' . $params['school2'] . '%'];
        }
        if(isset($params['typename']) && $params['typename'] != ''){
            $where['good_type'] = ['like', '%' . $params['typename'] . '%'];
        }
        if(isset($params['year']) && $params['year'] != ''){
            $where['sg_year'] = $params['year'];
        }
        if(isset($params['status']) && $params['status'] != ''){
            $where['sg_status'] = $params['status'];
        }
        if(isset($params['start'])&&$params['start']!=""&&isset($params['end'])&&$params['end']=="")
        {
            $where['sg_updatetime'] = ['>= time',$params['start']];
        }
        if(isset($params['end'])&&$params['end']!=""&&isset($params['start'])&&$params['start']=="")
        {
            $where['sg_updatetime'] = ['<= time',$params['end']];
        }
        if(isset($params['start'])&&$params['start']!=""&&isset($params['end'])&&$params['end']!="")
        {
            $where['sg_updatetime'] = ['between time',[$params['start'],$params['end']]];
        }
        $field = [
            'school_name','sg_school_erp','good_name','good_type', 'sg_good_coding','sg_sign',
            'sg_year','sg_sex','sg.good_price','good_thumb','sg_time','sg_updatetime','sg_status'
        ];
        $data = Db::table('up_school_good')->alias('sg')
            ->join('up_school s','sg.sg_school_erp=s.school_erp','LEFT')
            ->join('up_goods g','sg.sg_good_coding=g.good_coding','LEFT')
            ->where($where)
            ->order('sg_updatetime desc')
            ->field($field)
            ->select();
        if($data){
            $cellname = [
                '学校名称', '学校标识', '校服名字','校服类别', '校服标识', '校标',
                '年份', '性别', '价格', '图片地址', '上传时间', '更新时间', '状态'
            ];
            $status = ['sg_sex','sg_status'];
            $values = [
                'sg_sex' => [1 => '男',2 => '女',3 => '通用'],
                'sg_status' => [1 => '正常',2 => '冻结']
            ];
            $res = exportExcel('order',time(),$cellname,$data,$status,$values);
            return json($res);
        }else{
            return json(['code'=>0,'msg'=>'此条件下无数据']);
        }
    }
}